﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace SIS.DataAccess
{
	/// <summary>
	/// Author: hieunv
	/// </summary>
	class RegisterCourse : DataTable
	{
		public const string STUDENT_ID = "Id";
		public const string STUDENT_NAME = "StudentName";
		public const string COURSE_ID = "CourseId";
		public const string SECTION_ID = "SectionId";
		public const string SEMESTER = "Semester";
		public const string YEAR = "Year";
		public const string TITLE = "Title";
		public const string TIME_SLOT_ID = "TimeSlotId";
		public const string ROOM_NUMBER = "RoomNumber";
		public const string BUILDING = "Building";
		public const string INSTRUCTOR_NAME = "InstructorName";
		public const string DEPARTMENT_NAME = "DepartmentName";

		public RegisterCourse()
			: base("RegisterCourse")
		{
			var id = new DataColumn(STUDENT_ID, typeof(string));
			id.AllowDBNull = false;
			this.Columns.Add(id);

			var studentName = new DataColumn(STUDENT_NAME, typeof(string));
			studentName.AllowDBNull = false;
			this.Columns.Add(studentName);

			var courseId = new DataColumn(COURSE_ID, typeof(string));
			courseId.AllowDBNull = false;
			this.Columns.Add(courseId);

			var sectionId = new DataColumn(SECTION_ID, typeof(string));
			sectionId.AllowDBNull = false;
			this.Columns.Add(sectionId);

			var semester = new DataColumn(SEMESTER, typeof(string));
			semester.AllowDBNull = false;
			this.Columns.Add(semester);
			
			var year = new DataColumn(YEAR, typeof(int));
			year.AllowDBNull = false;
			this.Columns.Add(year);

			var title = new DataColumn(TITLE, typeof(string));
			this.Columns.Add(title);
			
			var timeSlotId = new DataColumn(TIME_SLOT_ID, typeof(string));
			this.Columns.Add(timeSlotId);

			var roomNumber = new DataColumn(ROOM_NUMBER, typeof(string));
			this.Columns.Add(roomNumber);

			var building = new DataColumn(BUILDING, typeof(string));
			this.Columns.Add(building);
			
			var instructorName = new DataColumn(INSTRUCTOR_NAME, typeof(string));
			this.Columns.Add(instructorName);

			var departmentName = new DataColumn(DEPARTMENT_NAME, typeof(string));
			this.Columns.Add(departmentName);
		}

		public void Fill(String name, String value)
		{
			this.Clear();
			SqlParameter parameter1 = new SqlParameter();
			parameter1.ParameterName = "@name";
			parameter1.SqlDbType = System.Data.SqlDbType.VarChar;
			parameter1.Value = name;
			SqlParameter parameter2 = new SqlParameter();
			parameter2.ParameterName = "@value";
			parameter2.SqlDbType = System.Data.SqlDbType.VarChar;
			List<SqlParameter> parameters = new List<SqlParameter>();
			parameters.Add(parameter1);
			parameters.Add(parameter2);
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "sp_LOC_DU_LIEU", parameters, this);
		}

		public void Fill(Arguments arguments)
		{
			this.Clear();
			SqlParameter args = new SqlParameter();
			args.ParameterName = "@args";
			args.SqlDbType = SqlDbType.Structured;
			args.Value = arguments;
			List<SqlParameter> parameters = new List<SqlParameter>();
			parameters.Add(args);
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.StoredProcedure, "sp_LOC_DU_LIEU_", parameters, this);

		}

		public void Fill()
		{
			this.Clear();
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT * FROM RegisterCourse ORDER BY Id", null, this);
		}

		public DataTable GetRegister()
		{
			this.Clear();
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT * FROM RegisteredCourse", null, this);
			return this;
		}

		public DataTable GetRegister(string studentId)
		{
			this.Clear();
			List<SqlParameter> parameters = new List<SqlParameter>();
			var p1 = new SqlParameter("@a1", SqlDbType.VarChar, 5, STUDENT_ID);
			p1.Value = studentId;
			parameters.Add(p1);
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT * FROM RegisteredCourse WHERE Id=@a1;", parameters, this);
			return this;
		}

		public static DataTable GetIds()
		{
			var ids = new DataTable();
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT DISTINCT Id FROM RegisterCourse ORDER BY Id", null, ids);
			return ids;
		}

		public static DataTable GetStudentName()
		{
			var students = new DataTable();
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT DISTINCT StudentName FROM RegisterCourse ORDER BY StudentName", null, students);
			return students;
		}

		public static DataTable GetSemester()
		{
			var semester = new DataTable();
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT DISTINCT Semester FROM RegisterCourse ORDER BY Semester", null, semester);
			return semester;
		}

		public static DataTable GetYear()
		{
			string query = "SELECT DISTINCT Year FROM RegisterCourse ORDER BY Year";
			var year = new DataTable();
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, query, null, year);
			return year;
		}

		public static DataTable GetCourses()
		{
			var courses = new DataTable();
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT DISTINCT CourseId FROM RegisterCourse ORDER BY CourseId", null, courses);
			return courses;
		}
	}
}
